

<DebuggerDisplay("{FieldClause}")> _
Class FieldInfo

    Public ReadOnly Name As String
    Public ReadOnly FMType As String
    Public Type As String
    Public Size As Integer = 0
    Public AllowsNull As Boolean = True
    Public Rows As New List(Of String)

    Public Sub New(ByVal name As String, ByVal type As String, ByVal allowsNull As Boolean)
        Me.Name = name
        Me.FMType = type
        Me.AllowsNull = allowsNull
        Select Case FMType
            Case "TEXT"
                Me.Type = "text"
            Case "NUMBER"
                Me.Type = "float"
            Case "DATE"
                Me.Type = "datetime"
            Case "TIME"
                Me.Type = "time"
            Case Else

        End Select
    End Sub

    Public Function FieldSql() As String
        Return String.Format("[{0}]", Trim(Name))
    End Function
    Public Function FieldClause() As String
        Dim sizeT As String

        If Type = "nvarchar" Then
            If Size = 0 Then
                sizeT = "(1)"
            Else
                sizeT = String.Format("({0})", Size)
            End If
        Else
            sizeT = ""
        End If
        Return String.Format("{0} {1}{2} {3}", FieldSql(), Type, sizeT, If(AllowsNull, "NULL", "NOT NULL"))
    End Function

    ReadOnly Property Value(ByVal i As Integer) As String
        Get
            Dim result As String
            If Rows(i) Is Nothing Then
                result = "NULL"
            Else
                If FMType = "NUMBER" Then
                    If Rows(i) = "" Then
                        result = "NULL"
                    Else
                        result = Rows(i).Replace(",", ".")
                        If Not IsNumeric(result) Then
                            ' Intentamos coger el último valor de un repetitivo
                            Dim sa = Split(result)
                            result = ""
                            For Each s In sa
                                If s <> "" AndAlso IsNumeric(s) Then
                                    result = s
                                End If
                            Next
                            If result = "" Then
                                result = "NULL"
                            End If
                        End If
                    End If
                ElseIf FMType = "DATE" Then
                    If Rows(i) = "" Then
                        result = "NULL"
                    Else
                        Dim fecha As Date
                        If Date.TryParse(Rows(i), fecha) Then
                            If fecha.Year > 1900 Then
                                result = String.Format("'{0:s}'", fecha)
                            ElseIf 0 <= fecha.Year AndAlso fecha.Year <= 20 Then
                                fecha = fecha.AddYears(2000)
                                result = String.Format("'{0:s}'", fecha)
                            ElseIf 950 <= fecha.Year AndAlso fecha.Year <= 1020 Then
                                fecha = fecha.AddYears(1000)
                                result = String.Format("'{0:s}'", fecha)
                            Else
                                result = "NULL"
                            End If
                        Else
                            Console.WriteLine("Fecha intraducible: {0} (Campo {1})", Rows(i), Name)
                            result = "NULL"
                        End If
                    End If
                ElseIf FMType = "TIME" Then
                    If Rows(i) = "" Then
                        result = "NULL"
                    Else
                        Dim hora As Date
                        If Date.TryParse(Rows(i), hora) Then
                            result = String.Format("'{0:HH:mm:ss}'", hora)
                        Else
                            result = "NULL"
                        End If
                    End If
                Else
                    result = String.Format("'{0}'", Rows(i).Replace("'", "''"))
                    End If
            End If

            If String.IsNullOrEmpty(result) Then
                Throw New Exception("ASDF")
            End If
            Return result
        End Get
    End Property
End Class
